﻿CREATE PROCEDURE [dbo].[sp_Get_ZipHistoryByCountyAndCity]
(
	@AccountID varchar(10),
	@Month INT,
	@Year INT,
	@County varchar(1000),
	@City varchar(max)
)
AS
BEGIN
	SET NOCOUNT ON
	
	select zh.ZipID, substring(zh.Zip,1,5) as ZipName, zl.County, zh.MdNumber, 
	zl.State, zl.City, zl.Division, zl.Market, zl.Territory,
	zl.ActiveLeadCount, zl.LapsedLeadCount, zl.BusinesLeadCount, zl.NeighborLeadCount,
	zh.OneALN, zh.OneBus, zh.TwoALN, zh.TwoBus, zh.ThreeALN, zh.ThreeBus, zh.FourALN, zh.FourBus, zh.FiveALN, zh.FiveBus, zh.SixALN, zh.SixBus, 
	zh.Zip, zl.Latitude, zl.Longitude, zl.AgentName, zl.ActiveStopCount, zl.LapsedStopCount, zl.PreferCity, zh.OrderDate, zl.AgentName, zl.MaxPcLeadType
	from (
		select z.ZipID, zi.MdNumber, z.Zip,
		sum(case when o.PeroidMonth = @Month and o.PeroidYear = @Year and (oz.Type = 0 or oz.Type = 1) then 1 else 0 end) as OneALN,
		sum(case when o.PeroidMonth = @Month and o.PeroidYear = @Year and (oz.Type = 0 or oz.Type = 2) then 1 else 0 end) as OneBus,
		sum(case when o.PeroidMonth = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -1, 0) and 
		o.PeroidYear = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -1, 1) and (oz.Type = 0 or oz.Type = 1) and o.IsPurchased = 1 then 1 else 0 end) as TwoALN,
		sum(case when o.PeroidMonth = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -1, 0) and 
		o.PeroidYear = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -1, 1) and (oz.Type = 0 or oz.Type = 2) and o.IsPurchased = 1 then 1 else 0 end) as TwoBus,
		sum(case when o.PeroidMonth = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -2, 0) and 
		o.PeroidYear = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -2, 1) and (oz.Type = 0 or oz.Type = 1) and o.IsPurchased = 1 then 1 else 0 end) as ThreeALN,
		sum(case when o.PeroidMonth = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -2, 0) and 
		o.PeroidYear = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -2, 1) and (oz.Type = 0 or oz.Type = 2) and o.IsPurchased = 1 then 1 else 0 end) as ThreeBus,
		sum(case when o.PeroidMonth = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -3, 0) and 
		o.PeroidYear = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -3, 1) and (oz.Type = 0 or oz.Type = 1) and o.IsPurchased = 1 then 1 else 0 end) as FourALN,
		sum(case when o.PeroidMonth = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -3, 0) and 
		o.PeroidYear = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -3, 1) and (oz.Type = 0 or oz.Type = 2) and o.IsPurchased = 1 then 1 else 0 end) as FourBus,
		sum(case when o.PeroidMonth = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -4, 0) and 
		o.PeroidYear = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -4, 1) and (oz.Type = 0 or oz.Type = 1) and o.IsPurchased = 1 then 1 else 0 end) as FiveALN,
		sum(case when o.PeroidMonth = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -4, 0) and 
		o.PeroidYear = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -4, 1) and (oz.Type = 0 or oz.Type = 2) and o.IsPurchased = 1 then 1 else 0 end) as FiveBus,
		sum(case when o.PeroidMonth = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -5, 0) and 
		o.PeroidYear = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -5, 1) and (oz.Type = 0 or oz.Type = 1) and o.IsPurchased = 1 then 1 else 0 end) as SixALN,
		sum(case when o.PeroidMonth = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -5, 0) and 
		o.PeroidYear = dbo.sf_GetPreviousMonthOrYear(@Year, @Month, -5, 1) and (oz.Type = 0 or oz.Type = 2) and o.IsPurchased = 1 then 1 else 0 end) as SixBus,
		max(zo.OrderDate) as OrderDate
		from dbo.Zips z
		inner join dbo.ZipInfo zi on zi.ZipID = z.ZipID
		left join dbo.OrderZip oz on z.ZipID = oz.ZipID
		left join dbo.Orders o on o.OrderID = oz.OrderID
		left join
		(
			select z.ZipID as ZipID, max(dbo.sf_GetOrderDate(o.PeroidYear, o.PeroidMonth)) as OrderDate from dbo.Zips z
			inner join dbo.ZipInfo zi on zi.ZipID = z.ZipID
			inner join dbo.OrderZip oz on z.ZipID = oz.ZipID 
			inner join dbo.Orders o on o.OrderID = oz.OrderID and o.IsPurchased = 1
			where zi.MdNumber = @AccountID and (o.PeroidMonth <> @Month or o.PeroidYear <> @Year)
			group by z.ZipID
		)zo on z.ZipID = zo.ZipID
		where zi.MdNumber = @AccountID 
		and zi.County in (select subcolstr from udf_CharlistToTable(@County, ','))
		and zi.City in (select subcolstr from udf_CharlistToTable(@City, ','))
		group by z.ZipID, z.Zip, zi.MdNumber
	)zh inner join ZipInfo zl
	on zh.ZipID = zl.ZipID
	where zl.MdNumber = @AccountID order by zl.County, zl.City, zh.Zip
END
